package net.j4love.mybatis.example;

import net.j4love.mybatis.dao.DepartmentDAO;
import net.j4love.mybatis.dao.EmployeeDAO;
import net.j4love.mybatis.dao.InterestDAO;
import net.j4love.mybatis.dao.UserDAO;
import net.j4love.mybatis.model.Department;
import net.j4love.mybatis.model.Employee;
import net.j4love.mybatis.model.Interest;
import net.j4love.mybatis.model.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author he peng
 * @create 2017/11/7 18:15
 * @see
 */
public class UpdateStatementTest {

    private static final Logger LOG = LoggerFactory.getLogger(UpdateStatementTest.class);

    private SqlSessionFactory sqlSessionFactory;

    private long startTime;

    private static final int COUNT = 100000;

    @Before
    public void init() throws Exception {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        this.sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        this.startTime = System.currentTimeMillis();
    }

    @After
    public void after() throws Exception {
        LOG.trace("======> cost time = {} ms" , (System.currentTimeMillis() - this.startTime));
    }

    @Test
    public void psBatchExecute() throws Exception {

        SqlSession sqlSession = this.sqlSessionFactory.openSession(true);
        Connection connection = sqlSession.getConnection();
        connection.setAutoCommit(false);
        String sql = "INSERT INTO " +
                     "`user` (nickname,realname,phone,login_password,pay_password,create_time,update_time)" +
                    "VALUES (?,?,?,?,?,?,?)";
        PreparedStatement ps = connection.prepareStatement(sql);

        for (int i = 0;i < COUNT ;i++) {
            ps.setString(1,"jdbc-batch" + i);
            ps.setString(2,"杰森伯恩" + i);
            ps.setLong(3,13723236323L);
            ps.setString(4,"123456789");
            ps.setString(5,"34dsadasdjhaskud893hdak32");
            ps.setDate(6,new java.sql.Date(System.currentTimeMillis()));
            ps.setDate(7,new java.sql.Date(System.currentTimeMillis()));
//
            ps.addBatch();
            if (i % 10000 == 0) {
                ps.executeBatch();
                ps.clearBatch();
            }
        }
        ps.executeBatch();
        connection.commit();

//        cost time = 1245 ms no addBatch
//        cost time = 1367 ms addBatch

        // cost time = 20949 ms
        // cost time = 21258 ms
        // 事务手动提交 7976 ms 性能大幅度提升

        // COUNT = 300000 , cost time = 17737 ms
        // COUNT = 500000 , cost time = 29070 ms
        // COUNT = 900000 , cost time = 51400 ms

    }

    @Test
    public void insertBatch() throws Exception {
        List<User> users = new ArrayList<>();
        User user;
        for (int i = 0;i < COUNT ;i++) {
            user = new User();
            user.setCreateTime(new Date());
            user.setUpdateTime(new Date());
            user.setNickname("mybatis-batch" + i);
            user.setRealname("杰森伯恩" + i);
            user.setPhone(13723236323L);
            user.setLoginPassword("123456789");
            user.setPayPassword("123456");

            users.add(user);
        }

        SqlSession session = this.sqlSessionFactory.openSession(false);
        UserDAO userDAO = session.getMapper(UserDAO.class);
        userDAO.insertBatch(users);
        session.commit();


        // ======> cost time = 6624 ms
        // mybatis 的性能损耗很小 ， 几乎可以忽略不计

        // COUNT = 100000 , REUSE Executor cost time = 8976 ms
        // COUNT = 300000 , REUSE Executor , cost time = 35153 ms

        // COUNT = 100000 , BATCH Executor cost time = 7770 ms
        // COUNT = 300000 , BATCH Executor , cost time = 24503 ms
    }

    @Test
    public void insertDeptAndEmp() throws Exception {

        Department dep = new Department();
        dep.setName("产品部")
           .setAddr("上海")
           .setCreateTime(new Date())
           .setUpdateTime(new Date());

        SqlSession session = this.sqlSessionFactory.openSession(true);
        DepartmentDAO departmentDAO = session.getMapper(DepartmentDAO.class);
        departmentDAO.insert(dep);

        Employee emp0 = new Employee();
        emp0.setName("tom")
           .setAge(23)
           .setDeptId(dep.getId())
           .setCreateTime(new Date())
           .setUpdateTime(new Date());

        Employee emp1 = new Employee();
        emp1.setName("jerry")
                .setAge(23)
                .setDeptId(dep.getId())
                .setCreateTime(new Date())
                .setUpdateTime(new Date());


        EmployeeDAO employeeDAO = session.getMapper(EmployeeDAO.class);
        employeeDAO.insert(emp0);
        employeeDAO.insert(emp1);

    }

    @Test
    public void insertInterest() throws Exception {
        User user = new User();
        user.setCreateTime(new Date());
        user.setUpdateTime(new Date());
        user.setNickname("钢铁侠");
        user.setRealname("杰森伯恩");
        user.setPhone(13723236323L);
        user.setLoginPassword("123456789");
        user.setPayPassword("123456");

        SqlSession session = this.sqlSessionFactory.openSession(true);
        UserDAO userDAO = session.getMapper(UserDAO.class);
        userDAO.insert(user);

        Interest interest1 = new Interest();
        interest1.setUserId(user.getId())
                .setCategory("运动")
                .setDescribe("篮球")
                .setCreateTime(new Date());
        InterestDAO interestDAO = session.getMapper(InterestDAO.class);
        interestDAO.insert(interest1);

        Interest interest2 = new Interest();
        interest2.setUserId(user.getId())
                .setCategory("运动")
                .setDescribe("篮球")
                .setCreateTime(new Date());
        interestDAO.insert(interest2);
    }
}
